<?php

/**
 * ECSHOP
 * ============================================================================
 * 版权所有 2005-2010 上海商派网络科技有限公司，并保留所有权利。
 * 网站地址: http://www.ecshop.com；
 * ----------------------------------------------------------------------------
 * 这不是一个自由软件！您只能在不用于商业目的的前提下对程序代码进行修改和
 * 使用；不允许对程序代码以任何形式任何目的的再发布。
 * ============================================================================
 *
 * $Author: liuhui $
 * $Id: goods_export.php 17107 2010-04-15 03:29:13Z liuhui $
*/

define('IN_ECS', true);

require(dirname(__FILE__) . '/includes/init.php');


if ($_REQUEST['act'] == 'purchase_export')
{
    /* 检查权限 */
    admin_priv('purchase_export');
    $start_date = date("Y-m-d",strtotime("-1 year"));   
    $smarty->assign('start_date', $start_date);
     /* 供货商名 */
    $suppliers_list_name = suppliers_list_name();
    $suppliers_exists = 1;
    if (empty($suppliers_list_name))
    {
        $suppliers_exists = 0;
    }
    $smarty->assign('suppliers_id', $suppliers_id);
    $smarty->assign('suppliers_exists', $suppliers_exists);
    $smarty->assign('suppliers_list_name', $suppliers_list_name);
    

    $smarty->assign('ur_here',             $_LANG['14_purchase_export']);
    $smarty->assign('cat_list', cat_list());
    $smarty->assign('brand_list',   get_brand_list());
    $smarty->assign('goods_type_list',  goods_type_list(0));
    $goods_fields = my_array_merge($_LANG['custom'], get_attributes());
    $data_format_array = array( 'magento'   => $_LANG['export_magento'],
                                'custom'    => $_LANG['export_custom'],
                               );
    $smarty->assign('data_format', $data_format_array);
    $smarty->assign('goods_fields', $goods_fields);
    assign_query_info();
    $smarty->display('purchase_export.htm');
}

//darren add for export purchase data
elseif($_REQUEST['act'] == 'act_export_magento')
{	
	set_time_limit(0);
	ini_set("memory_limit","356M");
    /* 检查权限 */
    admin_priv('purchase_export');
    $charset = empty($_POST['charset']) ? 'UTF8' : trim($_POST['charset']);
    include_once('includes/cls_phpzip.php');
    require(dirname(__FILE__) . '/includes/phpexcel7/Classes/PHPExcel.php');
    require(dirname(__FILE__) . '/includes/phpexcel7/Classes/PHPExcel/Writer/Excel2007.php');
    
    // Create new PHPExcel object
    $phpexcel = new PHPExcel();
    $phpexcel->setActiveSheetIndex(0);   
    $zip = new PHPZip;
    $where = get_export_where_sql($_POST);

   //产品列表导出单个或多个产品情况 add by darren 2011-09-26
   if($_REQUEST['goods_id']){

   	$where.= " and goods_id " . db_create_in($_REQUEST['goods_id']); 
   	
   }     
    //add by darren 0623
   $count_sql =  "SELECT count(1) " .
           " FROM " . $ecs->table('goods') . " AS g LEFT JOIN " . $ecs->table('brand') . " AS b ON g.brand_id = b.brand_id " .
           " LEFT JOIN " . $ecs->table('suppliers') . " AS s  ON s.suppliers_id = g.suppliers_id  " .
           " LEFT JOIN " . $ecs->table('goods_type') . " AS d  ON d.cat_id = g.goods_type  " .
           "" . $where;
   $db_stat = $db->getOne($count_sql); 
   $perfile = 300; 
   $for_stat  = ceil($db_stat/$perfile);   
   $for_stat  = $for_stat? $for_stat:1;
   $export_dir='../export/';  /*定义zip文件导出路径变量*/
   del_file($export_dir);	
   ob_end_clean();   
   print str_pad("",256);           
   echo "<p>export starting....<hr></p>";
   ob_flush();
   flush();
   
   for($i=0;$i<$for_stat;$i++):
    	echo 'creating file,from record '.($i*$perfile+1).'&nbsp;to&nbsp;'.($i+1)*$perfile.'......<br>';
	    ob_flush();
  		flush();	
	    $sql = "SELECT g.*, b.brand_name as brandname,s.suppliers_name as suppliers_name,d.cat_name as goods_type " .
	           " FROM " . $ecs->table('goods') . " AS g LEFT JOIN " . $ecs->table('brand') . " AS b ON g.brand_id = b.brand_id " .
	           " LEFT JOIN " . $ecs->table('suppliers') . " AS s  ON s.suppliers_id = g.suppliers_id  " .
	           " LEFT JOIN " . $ecs->table('goods_type') . " AS d  ON d.cat_id = g.goods_type  " .
	           "" . $where.' limit '.intval($i*$perfile).','.$perfile.' ';
	    $res = $db->query($sql);
	    /* XLS文件数组 */
	    $goods_value = array();
	   	$goods_value['goods_name'] =  '';
		$goods_value['brand_name'] =  '';
		$goods_value['product_model'] = '';
		$goods_value['standard'] = '';		
		$goods_value['technical_parameters'] = '';
		$goods_value['main_material'] = '';
		$goods_value['shop_price'] = '';		
		$goods_value['cost'] =  '';
		$goods_value['unit'] =  '';
		$goods_value['categories_of_taxes'] = '';
		$goods_value['tax'] = '"' . $row['tax'] ;
		$goods_value['min_sale_qty'] = '';
		$goods_value['state_of_origin'] = '';
		$goods_value['production_cycle'] = '';
		$goods_value['goods_weight'] = '';
		$goods_value['gross_weight'] = '';
		$goods_value['package_size'] = '';
		$goods_value['product_certification'] = '';		
			
		/**二次调整**/
		$goods_value['attribute_230'] = '';
		$goods_value['attribute_231'] = '';
		$goods_value['attribute_232'] = '';
		$goods_value['attribute_233'] = '';
		$goods_value['attribute_234'] = '';
		$goods_value['attribute_235'] = '';
		$goods_value['attribute_236'] = '';
		$goods_value['attribute_238'] = '';
		$goods_value['attribute_237'] = '';
		
		
		$goods_value['goods_desc'] ='';
		$goods_value['goods_brief'] ='';
		
		
		
		/**二次调整**/
		$goods_value['attribute_224'] = '';
		$goods_value['attribute_226'] = '';
		$goods_value['attribute_227'] = '';
		$goods_value['attribute_228'] = '';
		$goods_value['attribute_229'] = '';
	
		$goods_value['cat_id'] = '';
		$goods_value['goods_sn'] = '';
		$goods_value['goods_sn2'] = '';
		$goods_value['suppliers_name'] = '';
		$goods_value['hscode'] =  '';
		
		$goods_value['original_img'] = '';
		$goods_value['goods_img'] = '';
		$goods_value['goods_thumb'] = '';
		
		//add by hugo 2011-12-09
		$goods_value['type'] = '';
		$goods_value['simples_skus'] = '';
		$goods_value['configurable_attributes'] = '';
		
	
		if($charset=='UTF8'){
			array_walk($_LANG['magento'],create_function('&$n', '$n= mb_convert_encoding($n,  "CP936","UTF-8");;')); //编码转换
		}	 
		
	    $content = '"' . implode('","', $_LANG['magento']) . "\"\n";
	   
		$k=B;
		$phpexcel->getActiveSheet()->setCellValue('A1','Picture');
		foreach($_LANG['magento'] as $fileds) {
		    $phpexcel->getActiveSheet()->setCellValue($k.'1',$fileds);
		    $k++;
		}	
		
		$excel = new PHPExcel_Writer_Excel2007($phpexcel);      
	    $row_num=2;
	    while ($row = $db->fetchRow($res))
	    {
	 
	        
			//change      
			$goods_value['goods_name'] =  $row['goods_name'] ;
			$goods_value['brand_name'] =  $row['brandname'] ;
			$goods_value['product_model'] = $row['product_model'] ;
			$goods_value['standard'] = $row['standard'] ;
			
			$goods_value['technical_parameters'] = $row['technical_parameters'] ;
			$goods_value['main_material'] = $row['main_material'] ;
	
			
			$goods_value['shop_price'] = $row['shop_price'] ;
			
			$goods_value['cost'] =  $row['cost'] ;
			$goods_value['unit'] =  $row['unit'] ;
			$goods_value['categories_of_taxes'] = $row['categories_of_taxes'] ;
			$goods_value['tax'] = $row['tax'] ;
			$goods_value['min_sale_qty'] = $row['min_sale_qty'] ;
			$goods_value['state_of_origin'] = $row['state_of_origin'] ;
			$goods_value['production_cycle'] = $row['production_cycle'] ;
			$goods_value['goods_weight'] = $row['goods_weight'] ;
			$goods_value['gross_weight'] = $row['gross_weight'] ;
			$goods_value['package_size'] = $row['package_size'] ;
			$goods_value['product_certification'] = $row['product_certification'] ;
		 	$db_attributes = $db->getAll("select  attr_id,group_concat(attr_value) as attr_value from  " . $ecs->table('goods_attr') . " where goods_id='".$row['goods_id']."' group by goods_id,attr_id   ");
		 	foreach($db_attributes as $v){
		 		$attributes[$v['attr_id']] = $v['attr_value'] ;;
		 	}
		 	
		 	
			/**二次调整**/
			$goods_value['attribute_230'] = $attributes['230'];
			$goods_value['attribute_231'] = $attributes['231'];
			$goods_value['attribute_232'] = $attributes['232'];
			$goods_value['attribute_233'] = $attributes['233'];
			$goods_value['attribute_234'] = $attributes['234'];
			$goods_value['attribute_235'] = $attributes['235'];
			$goods_value['attribute_236'] = $attributes['236'];
			$goods_value['attribute_238'] = $attributes['238'];
			$goods_value['attribute_237'] = $attributes['237'];
			
			$goods_value['goods_desc'] = replace_special_char($row['goods_desc'], false) ;
			$goods_value['goods_brief'] = replace_special_char($row['goods_brief'], false) ;	
			
			
			/**二次调整**/
			$goods_value['attribute_224'] = $attributes['224'];
			$goods_value['attribute_226'] = $attributes['226'];
			$goods_value['attribute_227'] = $attributes['227'];
			$goods_value['attribute_228'] = $attributes['228'];
			$goods_value['attribute_229'] = $attributes['229'];
			
			$goods_value['cat_id'] = $row['cat_id'] ;
			$goods_value['goods_sn'] = $row['goods_sn'] ;
			$goods_value['goods_sn2'] = $row['goods_sn2'] ;
			$goods_value['suppliers_name'] = $row['suppliers_name'] ;
			$goods_value['hscode'] =  $row['hscode'] ;
			
			$goods_value['original_img'] = $row['original_img'] ;
			$goods_value['goods_img'] = $row['goods_img'] ;
			$goods_value['goods_thumb'] = $row['goods_thumb'] ;
			
			//add by hugo 2011-12-09
			$goods_value['type'] = $row['type'] ;
			$goods_value['simples_skus'] = $row['simples_skus'] ;
			$goods_value['configurable_attributes'] = $row['configurable_attributes'] ;
			
		    if($charset=='UTF8'){
				array_walk($goods_value,create_function('&$n', '$n= mb_convert_encoding($n,  "CP936","UTF-8");')); //编码转换
			}
			$imagefile = '../'.$row['original_img'];
			$setWidth = 22;
			$setHeight = 20;
			if(file_exists($imagefile)){
				$setHeight = 140;
			}
		    $k=B;         	
		    $phpexcel->getActiveSheet()->getColumnDimension('A')->setWidth($setWidth); 
            foreach($goods_value as $v) {
            	 
            	 $phpexcel->getActiveSheet()->getRowDimension($row_num)->setRowHeight($setHeight);
		   		 $phpexcel->getActiveSheet()->setCellValue($k.$row_num,$v);
		   		 $k++;
		     }
		    if(file_exists($imagefile) && is_file($imagefile))	{
		      	   
		    $objDrawing = new PHPExcel_Worksheet_Drawing();

			$objDrawing->setName('Photo');
			
			$objDrawing->setDescription('Photo');
		   
		    $objDrawing->setPath($imagefile);
			
			$objDrawing->setHeight(170);
			
			$objDrawing->setWidth(120);
			
			$objDrawing->setOffsetX(15);

			$objDrawing->setOffsetY(15);
						
			$objDrawing->setCoordinates('A'.$row_num);
			
			$objDrawing->setWorksheet($phpexcel->getActiveSheet());
		    
		    }	
			
          	$row_num++;		     	
          
	        /* 压缩图片 */
	        if (!empty($row['goods_img']) && is_file(ROOT_PATH . $row['goods_img']))
	        {
	           // $zip->add_file(file_get_contents(ROOT_PATH . $row['goods_img']), $row['goods_img']);
			   $goods_img[] = $row['goods_img'];
	        }
	        if (!empty($row['original_img']) && is_file(ROOT_PATH . $row['original_img']))
	        {
	            //$zip->add_file(file_get_contents(ROOT_PATH . $row['original_img']), $row['original_img']);
				$original_img[] = $row['original_img'];
	        }
	        if (!empty($row['goods_thumb']) && is_file(ROOT_PATH . $row['goods_thumb']))
	        {
	           //$zip->add_file(file_get_contents(ROOT_PATH . $row['goods_thumb']), $row['goods_thumb']);
			   $goods_thumb[] = $row['goods_thumb'];
	        }
					
	    }
	      	
	    $charset = empty($_POST['charset']) ? 'UTF8' : trim($_POST['charset']);		  
		sleep(0); 
		$excel->save("../export/purchase_export_".$i.".xlsx");
		
    endfor;
 	$goodszip = $export_dir."purchase_export.zip";		
	$zip->zip("../export",$goodszip); 
	echo "<hr><p>zip file created successfully!</p>";
    echo "<p>Memory,used&nbsp",convert_memory(memory_get_usage(true)),"</p>";
    echo "<span><a href=\"".$goodszip."\">click here to download</a></span>";
	
}

/* 处理Ajax调用 */
elseif ($_REQUEST['act'] == 'get_goods_fields')
{
    $cat_id = isset($_REQUEST['cat_id'])?intval($_REQUEST['cat_id']):0;
    $goods_fields = my_array_merge($_LANG['custom'], get_attributes($cat_id));
    make_json_result($goods_fields);
}

elseif ($_REQUEST['act'] == 'get_goods_list')
{
    include_once(ROOT_PATH . 'includes/cls_json.php');
    $json = new JSON;
    $filters = $json->decode($_REQUEST['JSON']);
    $arr = get_goods_list($filters);
    $opt = array();

    foreach ($arr AS $key => $val)
    {
        $opt[] = array('goods_id' => $val['goods_id'],
                        'goods_name' => $val['goods_name']
                      );
    }
    make_json_result($opt);
}


/**
 *
 *
 * @access  public
 * @param
 *
 * @return void
 */
function utf82u2($str)
{
    $len = strlen($str);
    $start = 0;
    $result = '';

    if ($len == 0)
    {
        return $result;
    }

    while ($start < $len)
    {
        $num = ord($str{$start});
        if ($num < 127)
        {
            $result .= chr($num) . chr($num >> 8);
            $start += 1;
        }
        else
        {
            if ($num < 192)
            {
                /* 无效字节 */
                $start ++;
            }
            elseif ($num < 224)
            {
                if ($start + 1 <  $len)
                {
                    $num = (ord($str{$start}) & 0x3f) << 6;
                    $num += ord($str{$start+1}) & 0x3f;
                    $result .=   chr($num & 0xff) . chr($num >> 8) ;
                }
                $start += 2;
            }
            elseif ($num < 240)
            {
                if ($start + 2 <  $len)
                {
                    $num = (ord($str{$start}) & 0x1f) << 12;
                    $num += (ord($str{$start+1}) & 0x3f) << 6;
                    $num += ord($str{$start+2}) & 0x3f;

                    $result .=   chr($num & 0xff) . chr($num >> 8) ;
                }
                $start += 3;
            }
            elseif ($num < 248)
            {

                if ($start + 3 <  $len)
                {
                    $num = (ord($str{$start}) & 0x0f) << 18;
                    $num += (ord($str{$start+1}) & 0x3f) << 12;
                    $num += (ord($str{$start+2}) & 0x3f) << 6;
                    $num += ord($str{$start+3}) & 0x3f;
                    $result .= chr($num & 0xff) . chr($num >> 8) . chr($num >>16);
                }
                $start += 4;
            }
            elseif ($num < 252)
            {
                if ($start + 4 <  $len)
                {
                    /* 不做处理 */
                }
                $start += 5;
            }
            else
            {
                if ($start + 5 <  $len)
                {
                    /* 不做处理 */
                }
                $start += 6;
            }
        }

    }

    return $result;
}

/**
 *
 *
 * @access  public
 * @param
 *
 * @return string
 */
function image_path_format($content)
{
    $prefix = 'http://' . $_SERVER['SERVER_NAME'];
    $pattern = '/(background|src)=[\'|\"]((?!http:\/\/).*?)[\'|\"]/i';
    $replace = "$1='" . $prefix . "$2'";
    return preg_replace($pattern, $replace, $content);
}

/**
 * 获取商品类型属性
 *
 * @param int $cat_id 商品类型ID
 *
 * @return array
 */
function get_attributes($cat_id = 0)
{
    $sql = "SELECT `attr_id`, `cat_id`, `attr_name` FROM " . $GLOBALS['ecs']->table('attribute') . " ";
    if (!empty($cat_id))
    {
        $cat_id = intval($cat_id);
        $sql .= " WHERE `cat_id` = '{$cat_id}' ";
    }
    $sql .= " ORDER BY `cat_id` ASC, `attr_id` ASC ";
    $attributes = array();
    $query = $GLOBALS['db']->query($sql);
    while ($row = $GLOBALS['db']->fetchRow($query))
    {
        $attributes[$row['attr_id']] = $row['attr_name'];
    }
    return $attributes;
}

/**
 * 设置导出商品字段名
 *
 * @param array $array 字段数组
 * @param array $lang 字段名
 *
 * @return array
 */
function set_goods_field_name($array, $lang)
{
    $tmp_fields = $array;
    foreach ($array as $key => $value)
    {
        if (isset($lang[$value]))
        {
            $tmp_fields[$key] = $lang[$value];
        }
        else
        {
            $tmp_fields[$key] = $GLOBALS['db']->getOne("SELECT `attr_name` FROM " . $GLOBALS['ecs']->table('attribute') . " WHERE `attr_id` = '" . intval($value) . "'");
        }
    }
    return $tmp_fields;
}

/**
 * 数组合并
 *
 * @param array $array1 数组1
 * @param array $array2 数组2
 *
 * @return array
 */
function my_array_merge($array1, $array2)
{
    $new_array = $array1;
    foreach ($array2 as $key => $val)
    {
        $new_array[$key] = $val;
    }
    return $new_array;
}

/**
 * 生成商品导出过滤条件
 *
 * @param array $filter 过滤条件数组
 *
 * @return string
 */
function get_export_where_sql($filter)
{
    $where = '';
  
    if (!empty($filter['goods_ids']))
    {
        $goods_ids = explode(',', $filter['goods_ids']);
        if (is_array($goods_ids) && !empty($goods_ids))
        {
            $goods_ids = array_unique($goods_ids);
            $goods_ids = "'" . implode("','", $goods_ids) . "'";
        }
        else
        {
            $goods_ids = "'0'";
        }
        $where = " WHERE g.is_delete = 0 AND g.is_on_sale = 1 AND g.goods_id IN (" . $goods_ids . ") ";
    }
    else
    {
        $_filter = new StdClass();
        $_filter->cat_id = $filter['cat_id'];
        $_filter->brand_id = $filter['brand_id'];
        $_filter->keyword = $filter['keyword'];
        $_filter->batch_num = $filter['batch_num'];
        $where = get_where_sql_export_assiant($_filter);
    }
    return $where;
}


/**
 * 生成过滤条件：用于 get_goodslist 和 get_goods_list
 * @param   object  $filter
 * @return  string
 */
function get_where_sql_export_assiant($filter)
{
    $time = date('Y-m-d');

    $where  = isset($filter->is_delete) && $filter->is_delete == '1' ?
        ' WHERE g.is_delete = 1 ' : ' WHERE g.is_delete = 0 ';
    $where .= (isset($filter->real_goods) && ($filter->real_goods > -1)) ? ' AND g.is_real = ' . intval($filter->real_goods) : '';
    $where .= isset($filter->cat_id) && $filter->cat_id > 0 ? ' AND ' . get_children($filter->cat_id) : '';
    $where .= isset($filter->brand_id) && $filter->brand_id > 0 ? " AND g.brand_id = '" . $filter->brand_id . "'" : '';
    $where .= isset($filter->intro_type) && $filter->intro_type != '0' ? ' AND ' . $filter->intro_type . " = '1'" : '';
    $where .= isset($filter->intro_type) && $filter->intro_type == 'g.is_promote' ?
        " AND g.promote_start_date <= '$time' AND g.promote_end_date >= '$time' " : '';
    $where .= isset($filter->keyword) && trim($filter->keyword) != '' ?
        " AND (g.goods_name LIKE '%" . mysql_like_quote($filter->keyword) . "%' OR g.goods_sn LIKE '%" . mysql_like_quote($filter->keyword) . "%' OR goods_id LIKE '%" . mysql_like_quote($filter->keyword) . "%') " : '';
    $where .= isset($filter->suppliers_id) && $filter->suppliers_id > 0 ? " AND g.suppliers_id = '" . $filter->suppliers_id . "'" : '';
    $where .= isset($filter->in_ids) ? ' AND g.goods_id ' . db_create_in($filter->in_ids) : '';
    $where .= isset($filter->exclude) ? ' AND g.goods_id NOT ' . db_create_in($filter->exclude) : '';
    $where .= isset($filter->stock_warning) ? ' AND g.goods_number <= g.warn_number' : '';

    return $where;
}





/**
 * 替换影响csv文件的字符
 *
 * @param $str string 处理字符串
 */
function replace_special_char($str, $replace = true)
{
    $str = str_replace("\r\n", "", image_path_format($str));
    $str = str_replace("\t", "    ", $str);
    $str = str_replace("\n", "", $str);
    if ($replace == true)
    {
        $str = '"' . str_replace('"', '""', $str) ;
    }
    return $str;
}


function convert_memory($size){
    $unit=array('b','kb','mb','gb','tb','pb');
    return @round($size/pow(1024,($i=floor(log($size,1024)))),2).' '.$unit[$i];
}

function del_file($dir)
{	
    $hand = opendir($dir);
    while($f = readdir($hand))
    {
		
        if($f=='.'||$f=='..') continue;
        if(is_dir($dir.$f))
        {          
            del_file($dir.$f .'/');
        }
        else
        {
            
            @unlink($dir . $f );
           
        }
    }
    
}

function cube($n){ 
	return "'" . mysql_real_escape_string($n). "'";
}

?>